package com.xiaoma.verbal.utils;

import java.io.FileNotFoundException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class ExcelUtil {

	/**
	 * 解析Excel文件（2003）
	 * 
	 * @param is
	 *            文件流
	 * @return 数据
	 * @throws Exception
	 */
	public static List<Map<String, Object>> parseExcel_2003(InputStream is)
			throws Exception {

		// Excel列数据存储容器,Map的key为 列号，value为列值
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		// 创建对Excel工作簿文件的引用
		HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(is));

		// 循环sheet
		for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
			// 获得一个sheet
			HSSFSheet sheet = workbook.getSheetAt(sheetNum);

			if (sheet == null) {
				continue;
			}
			// key为列号,value为头部编码======
			Map<Integer, String> headerCodeMap = new HashMap<Integer, String>();
			// 循环行
			for (int rowNumOfSheet = 0; rowNumOfSheet <= sheet.getLastRowNum(); rowNumOfSheet++) {

				// 获得一行
				HSSFRow row = sheet.getRow(rowNumOfSheet);
				if (row != null) {
					if (row.getCell(0) != null) {

						String question_num = getCellValue_2003(row.getCell(0));
						if (question_num.length() > 0) {
							if (!question_num.contains("_")&&!question_num.contains("-")) {
								if (rowNumOfSheet > 1) {
									throw new Exception("题号不存在");
								} else {
									continue;
								}
							}
						} else {
							continue;
						}
					} else {
						continue;
					}
				} else {
					continue;
				}
				// key为头部编码，value为值，比如：question_num:S_001
				Map<String, Object> rowMap = new HashMap<String, Object>();
				// 循环列
				for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) {
					Cell cell = row.getCell(cellNum);
					if (cell == null) {
						continue;
					}
					// 获取值
					String cellValue = getCellValue_2003(cell);

					if (rowNumOfSheet == 1) {
						// 带有头部编码的行
						if (cellValue != null) {
							headerCodeMap.put(cellNum, cellValue);
						}
					} else {
						// 数据行
						String headerCode = headerCodeMap.get(cellNum);
						if (headerCode != null) {
							rowMap.put(headerCode, cellValue);
						}
					}
				}
				if (rowNumOfSheet > 1) {
					list.add(rowMap);
				}
			}
		}
		is.close();
		return list;
	}

	/**
	 * 解析Excel文件（2007）
	 * 
	 * @param is
	 *            文件流
	 * @return 数据
	 * @throws Exception
	 */
	public static List<Map<String, Object>> parseExcel_2007(InputStream is)
			throws Exception {
		// Excel列数据存储容器,Map的key为 列号，value为列值
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		// 创建对Excel工作簿文件的引用
		XSSFWorkbook workbook = new XSSFWorkbook(is);

		// 循环sheet
		for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
			// 获得一个sheet
			XSSFSheet sheet = workbook.getSheetAt(sheetNum);

			if (sheet == null) {
				continue;
			}
			// key为列号,value为头部编码======
			Map<Integer, String> headerCodeMap = new HashMap<Integer, String>();
			// 循环行
			for (int rowNumOfSheet = 0; rowNumOfSheet <= sheet.getLastRowNum(); rowNumOfSheet++) {
				// 获得一行
				XSSFRow row = sheet.getRow(rowNumOfSheet);
				if (row != null) {
					if (row.getCell(0) != null) {

						String question_num = getCellValue_2007(row.getCell(0));
						if (question_num.length() > 0) {
							if (!question_num.contains("_")&&!question_num.contains("-")) {
								if (rowNumOfSheet > 1) {
									throw new Exception("题号不存在");
								} else {
									continue;
								}
							}
						} else {
							continue;
						}
					} else {
						continue;
					}

				} else {
					continue;
				}

				// key为头部编码，value为值，比如：question_num:S_001
				Map<String, Object> rowMap = new HashMap<String, Object>();

				// 循环列
				for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) {

					XSSFCell cell = row.getCell(cellNum);
					if (cell == null) {
						continue;
					}
					// 获取值
					String cellValue = getCellValue_2007(cell);

					if (rowNumOfSheet == 1) {
						// 带有头部编码的行
						if (cellValue != null) {
							headerCodeMap.put(cellNum, cellValue);
						}
					} else {
						// 数据行
						String headerCode = headerCodeMap.get(cellNum);
						if (headerCode != null) {
							rowMap.put(headerCode, cellValue);
						}
					}

				}
				if (rowNumOfSheet > 1) {
					list.add(rowMap);
				}
			}

		}
		is.close();
		return list;
	}

	/**
	 * 解析Excel文件（2003）
	 * 
	 * @param is
	 *            文件流
	 * @return 数据
	 * @throws Exception
	 */
	public static List<Map<String, Object>> parseCommonInfoExcel_2003(
			InputStream is) throws Exception {

		// Excel列数据存储容器,Map的key为 列号，value为列值
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		// 创建对Excel工作簿文件的引用
		HSSFWorkbook workbook = new HSSFWorkbook(is);

		// 循环sheet
		for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
			// 获得一个sheet
			HSSFSheet sheet = workbook.getSheetAt(sheetNum);

			if (sheet == null) {
				continue;
			}
			// key为列号,value为头部编码======
			Map<Integer, String> headerCodeMap = new HashMap<Integer, String>();
			// 循环行
			for (int rowNumOfSheet = 0; rowNumOfSheet <= sheet.getLastRowNum(); rowNumOfSheet++) {

				// 获得一行
				HSSFRow row = sheet.getRow(rowNumOfSheet);
				if (row != null) {

					// key为头部编码，value为值，比如：question_num:S_001
					Map<String, Object> rowMap = new HashMap<String, Object>();
					// 循环列
					for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) {
						Cell cell = row.getCell(cellNum);
						if (cell == null) {
							continue;
						}
						// 获取值
						String cellValue = getCellValue_2003(cell);

						if (rowNumOfSheet == 1) {
							// 带有头部编码的行
							if (cellValue != null) {

								headerCodeMap.put(cellNum, cellValue);
							}
						} else {
							// 数据行
							String headerCode = headerCodeMap.get(cellNum);
							if (headerCode != null) {
								rowMap.put(headerCode, cellValue);
							}
						}
					}
					if (rowNumOfSheet > 1) {
						list.add(rowMap);
					}
				}
			}
		}
		is.close();
		return list;
	}

	/**
	 * 解析Excel文件（2007）
	 * 
	 * @param is
	 *            文件流
	 * @return 数据
	 * @throws Exception
	 */
	public static List<Map<String, Object>> parseCommonInfoExcel_2007(
			InputStream is) throws Exception {

		// Excel列数据存储容器,Map的key为 列号，value为列值
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		// 创建对Excel工作簿文件的引用
		XSSFWorkbook workbook = new XSSFWorkbook(is);

		// 循环sheet
		for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
			// 获得一个sheet
			XSSFSheet sheet = workbook.getSheetAt(sheetNum);

			if (sheet == null) {
				continue;
			}
			// key为列号,value为头部编码======
			Map<Integer, String> headerCodeMap = new HashMap<Integer, String>();
			// 循环行
			for (int rowNumOfSheet = 0; rowNumOfSheet <= sheet.getLastRowNum(); rowNumOfSheet++) {
				// 获得一行
				XSSFRow row = sheet.getRow(rowNumOfSheet);
				if (row != null) {

					// key为头部编码，value为值，比如：question_num:S_001
					Map<String, Object> rowMap = new HashMap<String, Object>();

					// 循环列
					for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) {

						XSSFCell cell = row.getCell(cellNum);
						if (cell == null) {
							continue;
						}
						// 获取值
						String cellValue = getCellValue_2007(cell);

						if (rowNumOfSheet == 1) {
							// 带有头部编码的行
							if (cellValue != null) {
								headerCodeMap.put(cellNum, cellValue);
							}
						} else {
							// 数据行
							String headerCode = headerCodeMap.get(cellNum);
							if (headerCode != null) {
								rowMap.put(headerCode, cellValue);
							}
						}

					}
					if (rowNumOfSheet > 1) {
						list.add(rowMap);
					}
				}
			}

		}
		is.close();
		return list;
	}

	/**
	 * 获取Excel文件（2003）的列值
	 * 
	 * @param cell
	 *            列对象
	 * @return 列值
	 */
	private static String getCellValue_2003(Cell cell) {
		String cellValue = null;
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_NUMERIC:
			Double number = cell.getNumericCellValue();
			cellValue = number.toString();
			break;
		default:
			cellValue = cell.getStringCellValue();
			break;
		}
		return cellValue;
	}

	/**
	 * 获取Excel文件（2007）的列值
	 * 
	 * @param cell
	 *            列对象
	 * @return 列值
	 */
	private static String getCellValue_2007(XSSFCell cell) {
		String cellValue = null;
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_NUMERIC:
			Double number = cell.getNumericCellValue();
			cellValue = number.toString();
			break;
		// case HSSFCell.CELL_TYPE_BOOLEAN:
		// cellValue = cell.getBooleanCellValue();
		// break;
		default:
			cellValue = cell.getStringCellValue();
			break;
		}
		return cellValue;
	}

	
	
	
}
